/** (c) 2005-2007 Messaging Logic, All Rights Reserved. */
package org.xi8ix.jdbc;

import javax.sql.DataSource;
import java.io.*;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.net.URL;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * <p>Creates a pooled data source of JDBC connections using JDBC drivers. This simple implementation provides
 * an optional lightweight pooling mechanism. To automate the loading of drivers, the AutoDataSource loads
 * settings from Java java.util.Properties files with the following priority:</p>
 * <ol>
 * <li><strong>System properties</strong> - any properties accessible via System.getProperty().</li>
 * <li><strong>Custom file</strong> - an arbitrary file located using the propertyFile setting (see below).</li>
 * <li><strong>USER_HOME/.autodatasource/datasource.properties</strong> - loads a property file from a file
 *      relative to the user home directory. This property file is primarily useful for developers
 *      overriding the default during local testing.</p></li>
 * <li><strong>classpath</strong> - loads the classloader resource <tt>META-INF/sql/datasource.properties</tt>.
 *      This property file is useful for applications that wish to distribute hard coded data source properties
 *      in application jars (for example, when distributing applications containing embedded JDBC databases).</li>
 * </ol>
 * <p>If the properties exists, settings are loaded and the following property keys are used:</p>
 * <ul>
 *     <li><strong>driver</strong> - the fully qualified class name of the driver to load.</li>
 *     <li><strong>url</strong> - the connection URL to use when connecting.</li>
 *     <li><strong>user</strong> - the user name to use when connecting.</li>
 *     <li><strong>password</strong> - the password to use when connecting.</li>
 * </ul>
 * <p>The default behavior of the AutoDataSource can be altered by setting the following system properties
 * or by creating a classpath resource <tt>META-INF/sql/autodatasource.properties</tt>. System properties
 * override any settings found in the classpath resource. The following property keys are used:</p>
 * <ul>
 *      <li><strong>org.xi8ix.jdbc.AutoDataSource.propertyPrefix</strong> - the value will be a string used
 *          to prefix all property keys in the datasource.properties files and system properties. This allows
 *          properties like com.mycompany.jdbc.user to be used for user name by setting the prefix to
 *          <tt>com.mycompany.jdbc.</tt>.</li>
 *      <li><strong>org.xi8ix.jdbc.AutoDataSource.propertyFile</strong> - specifies a URL that will be
 *          used to load data source properties. If the property file is found at the URL, properties in
 *          that file are used in preference to all other properties.</li>
 * </ul>
 *
 * @author Iain Shigeoka
 */
public class AutoDataSource implements DataSource {
    private Logger LOG;
    public static String PROPERTY_PREFIX_KEY = AutoDataSource.class.getName()+".propertyPrefix";
    public static String PROPERTY_FILE_KEY = AutoDataSource.class.getName()+".propertyFile";
    final private long timeout = 60000;
    final private int poolsize = 10;
    private Vector<PoolTracker> connections = new Vector<PoolTracker>(poolsize);
    private PrintWriter logWriter;
    private int loginTimeout;
    private String driverClassName;
    private String url;
    private String user;
    private String password;
    private ClassLoader loader;
    private Driver driver;
    private Timer timer;
    private long delay = 10000;
    private long period = 5000;

    private static AutoDataSource singleton;

    /**
     * Create a data source using the provided JDBC driver.
     *
     * @param loader the classloader to use when obtaining resources and creating connection proxies
     * @param driver the JDBC driver to use
     */
    public AutoDataSource(ClassLoader loader, Driver driver) {
        this.driver = driver;
        this.loader = loader;
        setupProperties();
    }

    /**
     * Create a data source using either the current context class loader (if set), or this object's
     * classloader.
     *
     * There is only one data source created using this method (or any of the other
     * factory methods). Subsequent calls return the already created singleton.
     *
     * @return a data source ready for use
     */
    public synchronized static AutoDataSource getDataSource() {
        if (singleton == null) {
            singleton = new AutoDataSource(null,null);
        }
        return singleton;
    }

    /**
     * Create a data source using the provided class loader.
     *
     * There is only one data source created using this method (or any of the other
     * factory methods). Subsequent calls return the already created singleton.
     *
     * @param loader the classloader to use when obtaining resources and creating connection proxies
     * @return a data source ready for use
     */
    public synchronized static AutoDataSource getDataSource(ClassLoader loader) {
        if (singleton == null) {
            singleton = new AutoDataSource(loader, null);
        }
        return singleton;
    }

    /**
     * Create a data source using the provide class loader and driver.
     *
     * There is only one data source created using this method (or any of the other
     * factory methods). Subsequent calls return the already created singleton.
     *
     * @param loader the classloader to use when obtaining resources and creating connection proxies
     * @param driver the JDBC driver to use
     * @return a data source ready for use
     */
    public synchronized static AutoDataSource getDataSource(ClassLoader loader, Driver driver) {
        if (singleton == null) {
            singleton = new AutoDataSource(loader, driver);
        }
        return singleton;
    }

    /**
     * Return the JDBC driver class name, if any.
     *
     * @return the name of the jdbc driver class.
     */
    public String getDriverClassName() {
        String name;
        if (driver != null) {
            name = driver.getClass().getName();
        } else {
            name = driverClassName;
        }
        return name;
    }

    /**
     * Return the JDBC URL to use for accessing the DriverManager.
     *
     * @return the url used to connect to the driver
     */
    public String getUrl() {
        return url;
    }

    /**
     * Return the JDBC user to use for accessing the DriverManager.
     *
     * @return the user used to login to the server
     */
    public String getUser() {
        return user;
    }

    /**
     * Return the JDBC password to use for accessing the DriverManager.
     *
     * @return the password used to login to the server
     */
    public String getPassword() {
        return password;
    }

    /**
     * Obtain a JDBC connection using the data source defaults for username and password.
     *
     * @return a JDBC connection
     * @throws SQLException if there was a problem creating the connection
     */
    public synchronized Connection getConnection() throws SQLException {
        return getConnection(user,password);
    }

    /**
     * Obain a JDBC connecton using the provided username and password.
     *
     * @param username the username for the connection
     * @param password the password for the connection
     * @return a JDBC connection
     * @throws SQLException if there was a problem creating the connection
     */
    public Connection getConnection(String username, String password) throws SQLException {
        Connection connection = null;
        for (PoolTracker tracker: connections) {

            if (tracker.lease()) {
                connection = tracker.getProxy();
                break;
            }
        }
        if (connection == null) {
            PoolTracker tracker = getPoolTracker(username,password);
            tracker.lease();
            connection = tracker.getProxy();
        }
        return connection;
    }

    /**
     * Returns a collection of the outstanding pooled connections managed by this data source.
     *
     * @return a collection of pooled connections managed by this data source
     */
    public Collection<PoolTracker> getConnections() {
        return Collections.unmodifiableCollection(connections);
    }

    /**
     * Obtain the writer used to log data source messages.
     *
     * @return the writer used to log data source messages
     * @throws SQLException if there was a problem accessing the writer
     */
    public PrintWriter getLogWriter() throws SQLException {
        if (logWriter == null) {
            logWriter = new PrintWriter(System.out);
        }
        return logWriter;
    }

    /**
     * Set the writer used to log data source messages.
     *
     * @param logWriter the writer used to log data source messages
     * @throws SQLException if there was a problem setting the writer
     */
    public void setLogWriter(PrintWriter logWriter) throws SQLException {
        this.logWriter = logWriter;
    }

    /**
     * Set the timeout used by this data source.
     *
     * @param seconds the connection timeout in seconds
     * @throws SQLException if there was a problem setting the login timeout
     */
    public void setLoginTimeout(int seconds) throws SQLException {
        loginTimeout = seconds;
    }

    /**
     * Obtain the timeout used by this data source.
     *
     * @return the connection timeout in seconds
     * @throws SQLException if there was a problem setting the login timeout
     */
    public int getLoginTimeout() throws SQLException {
        return loginTimeout;
    }

    /**
     * Setup the data source properties according to the algorithm described in the class JavaDoc.
     */
    private void setupProperties() {
        // Setup the class loader
        if (loader == null) {
            this.loader = Thread.currentThread().getContextClassLoader();
            if (this.loader == null) {
                this.loader = this.getClass().getClassLoader();
            }
        }
        // Load configuration file
        Properties config = null;
        if (loader != null) {
            try {
                InputStream in = loader.getResourceAsStream("META-INF/sql/autodatasource.properties");
                if (in != null) {
                    config = new Properties();
                    config.load(in);
                }
            } catch (Exception e) {
                // ignored
            }
        }
        if (config == null) {
            config = System.getProperties();
        }
        String propertyPrefix = System.getProperty(PROPERTY_PREFIX_KEY,config.getProperty(PROPERTY_PREFIX_KEY,"")).trim();
        String propertyFile = System.getProperty(PROPERTY_FILE_KEY,config.getProperty(PROPERTY_FILE_KEY));

        // First try loading from system properties
        driverClassName =   System.getProperty(propertyPrefix+"driver");
        url =               System.getProperty(propertyPrefix+"url");
        user =              System.getProperty(propertyPrefix+"user");
        password =          System.getProperty(propertyPrefix+"password");

        // Try to load from custom file
        if ((driverClassName == null || url == null) && propertyFile != null) {
            try {
                URL fileUrl = new URL(propertyFile.trim());
                setupProperties(fileUrl.openStream(), propertyPrefix);
            } catch (Exception e) {
                // ignore
            }
        }
        // Try to load from home directory
        if (driverClassName == null || url == null) {
            try {
                File file = new File(System.getProperty("USER_HOME","")+"/.autodatasource/datasource.properties");
                if (file.exists()) {
                    setupProperties(new FileInputStream(file), propertyPrefix);
                }
            } catch (Exception e) {
                // ignore
            }
        }
        // try to load from classpath
        if ((driverClassName == null || url == null) && loader != null) {
            try {
                InputStream in = loader.getResourceAsStream("META-INF/sql/datasource.properties");
                if (in != null) {
                    setupProperties(in, propertyPrefix);
                }
            } catch (Exception e) {
                // ignore
            }
        }
        LOG = Logger.getLogger(propertyPrefix+".AutoDataSource");
    }

    /**
     * Setup any properties not already set using the properties file.
     *
     * @param in the input stream used to access the properties file
     * @param propertyPrefix the prefix used in all property key names
     * @throws IOException if there was a problem reading the property file
     */
    private void setupProperties(InputStream in, String propertyPrefix) throws IOException {
        Properties config = new Properties();
        config.load(in);
        if (driverClassName == null) {
            driverClassName = config.getProperty(propertyPrefix+"driver");
        }
        if (url == null) {
            url = config.getProperty(propertyPrefix+"url");
        }
        if (user == null) {
            user = config.getProperty(propertyPrefix+"user");
        }
        if (password == null) {
            password = config.getProperty(propertyPrefix+"password");
        }
    }

    /**
	 * Create a pool tracked connection.
     *
     * @param username the user used to login
     * @param password the password used to login
     * @return the pool tracker and associated connection created from the jdbc driver manager
     * @throws SQLException if there was a problem connection to the database
	 */
    private PoolTracker getPoolTracker(String username, String password) throws SQLException {
        Properties props = new Properties();
		if (username != null) {
			props.setProperty("user", username);
		}
		if (password != null) {
			props.setProperty("password", password);
		}
        if (driver == null) {

            String driverClassName = getDriverClassName();
            if (driverClassName == null) {
                throw new IllegalArgumentException("driverClassName must not be empty");
            }
            driverClassName = driverClassName.trim();
            try {
                Class driverClass = Class.forName(driverClassName, true, loader);
                driver = (Driver) driverClass.newInstance();
            }
            catch (ClassNotFoundException ex) {
                throw new IllegalArgumentException (
                        "Could not load JDBC driver class [" + driverClassName + "]", ex);
            } catch (InstantiationException e) {
                throw new IllegalArgumentException (
                        "Could not create JDBC driver [" + driverClassName + "]", e);
            } catch (IllegalAccessException e) {
                throw new IllegalArgumentException (
                        "Could not access JDBC driver [" + driverClassName + "]", e);
            }
            LOG.fine("Loaded JDBC driver: " + driverClassName);

            if (timer == null) {
                timer = new Timer("auto-data-source",true);
                timer.schedule(new TimerTask(){
                    public void run() {
                        reapConnections();
                    }
                },delay,period);
            }
        }
        LOG.log(Level.FINEST,"Creating new JDBC Connection to " + url,url);

        Connection connection = driver.connect(url, props);
        if(connection == null) {
            throw new SQLException("Driver could not create connection");
        }
        LOG.log(Level.FINEST,"Created JDBC Connection " + connection,connection);

        PoolTracker tracker;
        try {
            tracker = new PoolTracker(connection);
            LOG.log(Level.FINEST,"Created Connection tracker " + tracker,tracker);
            ConnectionInvocationHandler handler = new ConnectionInvocationHandler(tracker);
            LOG.log(Level.FINEST,"Created Connection handler " + handler,handler);
            Connection proxy = (Connection) Proxy.newProxyInstance(loader,new Class<?>[]{Connection.class},handler);
            LOG.log(Level.FINEST,"Created Connection proxy " + proxy,proxy);
            tracker.setProxy(proxy);
            connections.add(tracker);
            LOG.log(Level.FINEST,"Created tracker " + tracker,tracker);
        } catch (RuntimeException e) {
            LOG.throwing(AutoDataSource.class.getName(),"getPoolTracker",e);
            throw new SQLException(e.getMessage());
        }
        return tracker;
    }

    /**
     * Remove a tracker from the pool.
     *
     * @param tracker the tracker to be removed
     * @throws SQLException if there was a problem closing the tracker connection
     */
    private synchronized void removeTracker(PoolTracker tracker) throws SQLException {
        connections.remove(tracker);
        tracker.close();
    }

    /**
     * Return a tracker to the pool.
     *
     * @param tracker the tracker to return
     */
    private synchronized void returnTracker(PoolTracker tracker) {
        if (tracker != null) {
            tracker.expire();
        }
    }

    /**
     * Trim excessive idle connections.
     */
    private synchronized void reapConnections() {

        long stale = System.currentTimeMillis() - timeout;

        for (PoolTracker tracker: connections) {
            if (tracker.isExpired(stale)) {
                try {
                removeTracker(tracker);
                } catch (SQLException e) {
                    LOG.throwing(AutoDataSource.class.getName(),"reapConnections",e);
                }
            }
        }
        LOG.finest("Reaped connections");
    }

    /**
     * Creates a simple proxy for JDBC connections and intercepts calls to close() that
     * returns the connection to the pool rather than closing the underlyng connection.
     *
     * @author Iain Shigeoka
     */
    private class ConnectionInvocationHandler implements InvocationHandler {
        /** The tracker used to proxy calls. */
        private PoolTracker tracker;

        /**
         * Set the tracker used to proxy calls.
         *
         * @param tracker the tracker used to proxy calls.
         */
        private ConnectionInvocationHandler(PoolTracker tracker) {
            this.tracker = tracker;
        }

        /**
         * Proxy all calls except the connection close method which returns the connection to
         * the connection pool.
         *
         * @param proxy the proxy object being invoked
         * @param method the method being invoked
         * @param args the arguments for the call
         * @return the method return object
         * @throws Throwable if there was a problem making the call
         */
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            Object result;
            if ("close".equals(method.getName())) {
                returnTracker(tracker);
                result = null;
            } else {
                result = method.invoke(tracker.getConnection(),args);
            }
            return result;
        }
    }

    /**
     * The data required to track a pooled connection including the proxied connection, the underlying connection,
     * and connection lease information.
     *
     * @author Iain Shigeoka
     */
    public static class PoolTracker {
        /** True if the connection is currently in use. */
        private boolean inUse;
        /** The timestamp set when the connection was last used. */
        private long timestamp;
        /** The proxied connection. */
        private Connection proxy;
        /** The real connection. */
        private Connection connection;

        /**
         * Create a pool tracker with the real connection.
         *
         * @param connection the real JDBC connection
         */
        public PoolTracker(Connection connection) {
            this.connection = connection;
        }

        /**
         * Lease the connection, or return false if the connection has already been leased.
         *
         * @return true if the lease was successful, false if the connection is already leased
         */
        private synchronized boolean lease() {
            if (inUse) {
                return false;
            } else {
                inUse = true;
                timestamp = System.currentTimeMillis();
                return true;
            }
        }

        /**
         * Tests whether the underlying connection is valid.
         *
         * @return true if the connection is valid
         */
        public boolean validate() {
            try {
                connection.getMetaData();
            } catch (Exception e) {
                return false;
            }
            return true;
        }

        /**
         * Expires the current lease allowing the tracker to be leased again.
         */
        public void expire() {
            inUse = false;
        }

        /**
         * Sets the proxied connection object.
         *
         * @param proxy the connection to proxy
         */
        public void setProxy(Connection proxy) {
            this.proxy = proxy;
        }

        /**
         * Obtain the proxied connection object.
         *
         * @return returns the proxied connection object
         */
        public Connection getProxy() {
            return proxy;
        }

        /**
         * Obtain the underlying real JDBC connection object.
         *
         * @return returns the real connection object
         */
        public Connection getConnection() {
            return connection;
        }

        /**
         * Closes the underlying connection.
         *
         * @throws SQLException if there was a problem closing the connection
         */
        public void close() throws SQLException {
            connection.close();
        }

        /**
         * Tests whether this connection has expired (was leased but not closed). The connection
         * pool uses this test to ensure connections checked out of the pool are always closed
         * even if the connection user neglects to close the proxied connection.
         *
         * @param stale the unix time in milliseconds that indicates the connection is expired
         * @return true if the connection has expired
         */
        public boolean isExpired(long stale) {
            return inUse && stale > timestamp && !validate();
        }
    }
}
